﻿using System;
using Dapper;
using System.Data;
using System.Linq;
using System.Text;
using Ayatta.Contract;
using Ayatta.Service.Extension;
using Ayatta.Contract.Domain;
using Ayatta.Common.Extension;
using System.Collections.Generic;

namespace Ayatta.Service
{
    public class PassportService : BaseService, IPassportService
    {

        public int UserCreate(User o)
        {
            var result = 0;
            var template = new SqlBuilder()
                .Insert(Database.Passport.Table.User)
                .Values(o.Guid, o.Name, o.Nickname, o.Password, o.PayPassword, o.Role, o.Level, o.Limit, o.Permit, o.Email, o.Mobile, o.Avatar, o.Status, o.Authenticated, o.AuthenticatedOn, o.CreatedBy, o.CreatedOn)
                .Append("select cast(scope_identity() as int) as Id").Complete();

            var profile = o.Profile;
            var extra = o.Extra;
            var account = new Account();
            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            using (var transaction = connection.BeginTransaction())
            {
                try
                {
                    var id = connection.Query<int>(template.Sql, template.Param, transaction).FirstOrDefault();
                    if (id > 0)
                    {
                        account.Id = extra.Id = profile.Id = id;
                        var a = new SqlBuilder()
                            .Insert(Database.Passport.Table.UserProfile)
                            .Values(profile.Id, profile.Code, profile.Name, profile.Gender, profile.Marital,
                                    profile.Birthday, profile.Phone, o.Mobile, profile.AreaId, profile.Address,
                                    profile.SignUpIp, profile.SignUpBy, profile.TraceCode, profile.LastSignInIp,
                                    profile.LastSignInOn);

                        var b = new SqlBuilder()
                            .Insert(Database.Passport.Table.UserExtra)
                            .Values(extra.Id, extra.Game, extra.LastServerId);

                        var c = new SqlBuilder()
                            .Insert(Database.Account.Name + "." + Database.Account.Table.Account + " (Id)")
                            .Values(account.Id);

                        template = new SqlBuilder().Append(a).Append(b).Append(c).Complete();
                        connection.Execute(template.Sql, template.Param, transaction);
                        transaction.Commit();
                        result = id;
                    }
                    else
                    {
                        transaction.Rollback();
                    }
                }
                catch (Exception e)
                {
                    transaction.Rollback();
                    result = 0;
                    //throw;
                }
            }
            return result;
        }

        /// <summary>
        /// 用户登录
        /// </summary>
        /// <param name="name">用户名/绑定邮箱/绑定手机号</param>
        /// <param name="password">登录密码</param>
        /// <returns></returns>
        public User UserSignIn(string name, string password)
        {
            var sb = new StringBuilder();

            sb.AppendLine("if exists(select 0 from [User] a where a.[Name]=@Name and a.[Password]=@Password) begin select top 1 * from [User] a where a.[Name]=@Name and a.[Password]=@Password end");

            if (name.IsMobile())
            {
                sb.AppendLine("else if exists(select 0 from [User] a where a.[Mobile] is not null and a.[Mobile]=@Name and a.[Password]=@Password) begin select top 1 * from [User] a where a.[Mobile] is not null and a.[Mobile]=@Name and a.[Password]=@Password end");
            }
            else if (name.IsEmail())
            {
                sb.AppendLine("else if exists(select 0 from [User] a where a.[Email] is not null and a.[Email]=@Name and a.[Password]=@Password) begin select top 1 * from [User] a where a.[Email] is not null and a.[Email]=@Name and a.[Password]=@Password end");
            }

            sb.AppendLine("else begin select top 0 * from [User] where 1<>1 end");

            var template = new RegexSqlBuilder()
                .AddParameters(new { name, password }).Complete(sb.ToString());

            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Query<User>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        /// <summary>
        /// 获取用户信息
        /// </summary>
        /// <param name="id">Id</param>
        /// <param name="profile">是否包含UserProfile</param>
        /// <param name="extra">是否包含UserExtra</param>
        /// <returns></returns>
        public User UserGet(int id, bool profile = false, bool extra = false)
        {
            if (profile && !extra)
            {
                var template = new SqlBuilder().Select("*")
                                               .From(Database.Passport.Table.User + " a")
                                               .InnerJoin(Database.Passport.Table.UserProfile + " b on a.Id=b.Id")
                                               .Where("a.[Id]={0}", id).Complete();
                using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
                {
                    return connection.Query<User, UserProfile>(template.Sql, template.Param).FirstOrDefault();
                }
            }
            if (!profile && extra)
            {
                var template = new SqlBuilder().Select("*")
                                               .From(Database.Passport.Table.User + " a")
                                               .InnerJoin(Database.Passport.Table.UserExtra + " b on a.Id=b.Id")
                                               .Where("a.[Id]={0}", id).Complete();
                using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
                {
                    return connection.Query<User, UserExtra>(template.Sql, template.Param).FirstOrDefault();
                }
            }
            if (profile)
            {
                var template = new SqlBuilder().Select("*")
                                               .From(Database.Passport.Table.User + " a")
                                               .InnerJoin(Database.Passport.Table.UserProfile + " b on a.Id=b.Id")
                                               .InnerJoin(Database.Passport.Table.UserExtra + " c on a.Id=c.Id")
                                               .Where("a.[Id]={0}", id).Complete();
                using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
                {
                    return connection.Query<User, UserProfile, UserExtra>(template.Sql, template.Param).FirstOrDefault();
                }
            }
            else
            {
                var template = new SqlBuilder().Select("*")
                                               .From(Database.Passport.Table.User)
                                               .Where("[Id]={0}", id).Complete();
                using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
                {
                    return connection.Query<User>(template.Sql, template.Param).FirstOrDefault();
                }
            }
        }

        /// <summary>
        /// 获取用户信息
        /// </summary>
        /// <param name="guid">guid</param>
        /// <returns></returns>
        public User UserGetByGuid(string guid)
        {
            var template = new SqlBuilder().Select("*")
           .From(Database.Passport.Table.User)
           .Where("[Guid]={0}", guid).Complete();
            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Query<User>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public MagicKit<int> UserExist(string name)
        {
            var sql = @"if exists(select 0 from [User] where Name=@name) begin select cast(1 as bit) as [Status],Id as [Data],[Name] as [Message] from  [User] where Name=@name return end
            else if exists(select 0 from [User] where [Email] is not null and [Email] =@name) begin select cast(1 as bit) as [Status],Id as [Data],[Guid] as [Name] from [User] where [Email] is not null and [Email] =@name return end
            else if exists(select 0 from [User] where  [Mobile] is not null and [Mobile] =@name) begin select cast(1 as bit) as [Status],Id as [Data],[Guid] as [Name] from [User] where  [Mobile] is not null and [Mobile] =@name return end
            else begin select cast(0 as bit) as [Status],0 as [Data],'' as [Message]  return end";

            var template = new RegexSqlBuilder().Complete(sql, new { name });
            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Query<MagicKit<int>>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public bool UserEmailBind(int userId, string email)
        {
            var template = new SqlBuilder()
                .Update(Database.Passport.Table.User)
                .Set("[Email]={0}", email)
                .Where("[Id]={0}", userId)
                .Complete();

            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool UserMobileBind(int userId, string mobile)
        {
            var template = new SqlBuilder()
                .Update(Database.Passport.Table.User)
                .Set("[Mobile]={0}", mobile)
                .Where("[Id]={0}", userId)
                .Complete();

            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool UserAvatarUpdate(int userId, bool avatar)
        {
            var template = new SqlBuilder()
                .Update(Database.Passport.Table.User)
                .Set("[Avatar]={0}", avatar)
                .Where("[Id]={0}", userId)
                .Complete();
            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool UserNicknameUpdate(int userId, string nickname)
        {
            var template = new SqlBuilder()
                .Update(Database.Passport.Table.User)
                .Set("[Nickname]={0}", nickname)
                .Where("[Id]={0}", userId)
                .Complete();
            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool UserPasswordUpdate(int userId, string password)
        {
            var template = new SqlBuilder()
                .Update(Database.Passport.Table.User)
                .Set("[Password]={0}", password)
                .Where("[Id]={0}", userId)
                .Complete();

            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        #region PreUser

        public bool PreUserCreate(PreUser o)
        {
            var template = new SqlBuilder()
            .Insert(Database.Passport.Table.PreUser)
            .Values(o.Id, o.Origin, o.UserName, o.Password, o.Browser, o.UserAgent, o.IpAddress, o.UrlReferrer, o.TraceCode, o.CreatedBy, o.CreatedOn)
            .Complete();

            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public PreUser PreUserGet(string id)
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Passport.Table.PreUser)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Query<PreUser>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        #endregion

        #region UserProfile

        public bool UserProfileUpdate(UserProfile o, bool authenticated)
        {
            var builder = new RegexSqlBuilder()
                .Table(Database.Passport.Table.UserProfile)
                .Field("[Marital]=@Marital,[Phone]=@Phone,[Mobile]=@Mobile,[AreaId]=@AreaId,[Address]=@Address", new { o.Marital, o.Phone, o.Mobile, o.AreaId, o.Address })
                .Where("[Id] = @Id", new { o.Id });
            if (!authenticated)
            {
                builder.Field("[Code]=@Code,[Name]=@Name,[Gender]=@Gender,[Birthday]=@Birthday", new { o.Code, o.Name, o.Gender, o.Birthday });
            }
            var template = builder.Complete(RegexSqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }

        }

        public UserProfile UserProfileGet(int id)
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Passport.Table.UserProfile)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Query<UserProfile>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public PagedList<UserProfile> UserProfilePagedList(int page = 1, int size = 20)
        {
            var builder = new RegexSqlBuilder().Field("*")
            .Table(Database.Passport.Table.UserProfile)
            .OrderBy("[Id] DESC")
            .AddParameters(new { begin = (page - 1) * size + 1, end = page * size });
            /*
            builder.Where("[Status]=@Status", new { status });
            */
            var template = builder.Complete(RegexSqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.PagedList<UserProfile>(template);
            }
        }

        #endregion


        #region UserAddress

        public bool UserAddressCreate(UserAddress o)
        {
            var template = new SqlBuilder()
            .Insert(Database.Passport.Table.UserAddress)
            .Values(o.UserId, o.Name, o.Company, o.Category, o.Country, o.AreaId, o.Province, o.City, o.District, o.Address, o.PostalCode, o.Phone, o.Mobile, o.IsDefault, o.CreatedBy, o.CreatedOn)
            .Complete();

            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool UserAddressUpdate(UserAddress o)
        {
            var template = new SqlBuilder()
            .Update(Database.Passport.Table.UserAddress)
            .Set("[UserId]={0}", o.UserId)
            .Set("[Name]={0}", o.Name)
            .Set("[Company]={0}", o.Company)
            .Set("[Category]={0}", o.Category)
            .Set("[Country]={0}", o.Country)
            .Set("[AreaId]={0}", o.AreaId)
            .Set("[Province]={0}", o.Province)
            .Set("[City]={0}", o.City)
            .Set("[District]={0}", o.District)
            .Set("[Address]={0}", o.Address)
            .Set("[PostalCode]={0}", o.PostalCode)
            .Set("[Phone]={0}", o.Phone)
            .Set("[Mobile]={0}", o.Mobile)
            .Set("[IsDefault]={0}", o.IsDefault)
            .Where("[UserId]={0} AND [Id]={1}", o.UserId, o.Id).Complete();

            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool UserAddressDelete(int id, int? userId)
        {
            var template = new SqlBuilder()
            .Delete(Database.Passport.Table.UserAddress)
            .Where("[Id]={0}", id)
            .If(userId.HasValue && userId.Value > 0, "[UserId]={0}", userId)
            .Complete();

            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public UserAddress UserAddressGet(int id, int? userId)
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Passport.Table.UserAddress)
            .Where("[Id]={0}", id)
            .If(userId.HasValue && userId.Value > 0, "[UserId]={0}", userId)
            .Complete();

            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Query<UserAddress>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public IList<UserAddress> UserAddressList(int? userId)
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Passport.Table.UserAddress)
            .Where().If(userId.HasValue && userId.Value > 0, "[UserId]={0}", userId).Complete();

            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Query<UserAddress>(template.Sql, template.Param).ToList();
            }
        }

        public bool UserAddressSetDefault(int id, int userId, AddressCategory category)
        {
            var builder = new RegexSqlBuilder().AddParameters(new { id, userId, category });

            var sb = new StringBuilder();
            sb.AppendFormat("update {0} set [IsDefault]=0 where [UserId]=@UserId AND [Category]=@Category AND [Id]<>@Id;",
                            Database.Passport.Table.UserAddress);
            sb.AppendFormat("update {0} set [IsDefault]=1 where [UserId]=@UserId AND [Category]=@Category AND [Id]=@Id;",
                            Database.Passport.Table.UserAddress);

            var template = builder.Complete(sb.ToString());
            using (var connection = GetConnection(ConnectionName.Passport))
            {
                return connection.Execute(template.Sql, template.Param) > 0;
            }
        }

        #endregion

        #region UserInvoice

        public bool UserInvoiceCreate(UserInvoice o)
        {
            var template = new SqlBuilder()
            .Insert(Database.Passport.Table.UserInvoice)
            .Values(o.UserId, o.Category, o.Title, o.Content, o.IsDefault, o.CreatedBy, o.CreatedOn)
            .Complete();

            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool UserInvoiceUpdate(UserInvoice o)
        {
            var template = new SqlBuilder()
            .Update(Database.Passport.Table.UserInvoice)
            .Set("[UserId]={0}", o.UserId)
            .Set("[Category]={0}", o.Category)
            .Set("[Title]={0}", o.Title)
            .Set("[Content]={0}", o.Content)
            .Set("[IsDefault]={0}", o.IsDefault)
            .Where("[Id]={0}", o.Id).Complete();

            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool UserInvoiceDelete(int id)
        {
            var template = new SqlBuilder()
            .Delete(Database.Passport.Table.UserInvoice)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public UserInvoice UserInvoiceGet(int id)
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Passport.Table.UserInvoice)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Query<UserInvoice>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public IList<UserInvoice> UserInvoiceList(int? userId=null)
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Passport.Table.UserInvoice)
            .Where("1=1").Complete();

            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Query<UserInvoice>(template.Sql, template.Param).ToList();
            }
        }

        public PagedList<UserInvoice> UserInvoicePagedList(int page = 1, int size = 20)
        {
            var builder = new RegexSqlBuilder().Field("*")
            .Table(Database.Passport.Table.UserInvoice)
            .OrderBy("[Id] DESC")
            .AddParameters(new { begin = (page - 1) * size + 1, end = page * size });
            /*
            builder.Where("[Status]=@Status", new { status });
            */
            var template = builder.Complete(RegexSqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.PagedList<UserInvoice>(template);
            }
        }

        #endregion

        #region OAuth

        public bool OAuthCreate(OAuth o)
        {
            var template = new SqlBuilder()
            .Insert(Database.Passport.Table.OAuth)
            .Values(o.Name, o.ClientId, o.PublicKey, o.PrivateKey, o.Scope, o.CallbackUri, o.BaseUri, o.AuthorizationCodeResource, o.AccessTokenResource, o.UserInfoResource, o.Status, o.CreatedBy, o.CreatedOn)
            .Complete();

            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool OAuthUpdate(OAuth o)
        {
            var template = new SqlBuilder()
            .Update(Database.Passport.Table.OAuth)
            .Set("[Name]={0}", o.Name)
            .Set("[ClientId]={0}", o.ClientId)
            .Set("[PublicKey]={0}", o.PublicKey)
            .Set("[PrivateKey]={0}", o.PrivateKey)
            .Set("[Scope]={0}", o.Scope)
            .Set("[CallbackUri]={0}", o.CallbackUri)
            .Set("[BaseUri]={0}", o.BaseUri)
            .Set("[AuthorizationCodeResource]={0}", o.AuthorizationCodeResource)
            .Set("[AccessTokenResource]={0}", o.AccessTokenResource)
            .Set("[UserInfoResource]={0}", o.UserInfoResource)
            .Set("[Status]={0}", o.Status)
            .Where("[Id]={0}", o.Id).Complete();

            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool OAuthDelete(int id)
        {
            var template = new SqlBuilder()
            .Delete(Database.Passport.Table.OAuth)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public OAuth OAuthGet(int id)
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Passport.Table.OAuth)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Query<OAuth>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public IList<OAuth> OAuthList()
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Passport.Table.OAuth)
            .Where("1=1").Complete();

            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Query<OAuth>(template.Sql, template.Param).ToList();
            }
        }

        #endregion

        #region OAuthUser

        public void OAuthUserSignIn(OAuthUser o)
        {
            var param = new { o.Guid, o.Name, o.OpenId, o.Scope, o.AccessToken, o.RefreshToken, o.ExpirationTime, o.Extra, o.CreatedBy, o.CreatedOn };
            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                connection.Execute("[Proc.OAuth.User.SignIn]", param, commandType: CommandType.StoredProcedure);
            }
        }

        public OAuthUser OAuthUserGet(string openId, string createdBy)
        {
            var template = new SqlBuilder().Select("*")
           .From(Database.Passport.Table.OAuthUser)
           .Where("[OpenId]={0} and [CreatedBy]={1}", openId, createdBy).Complete();
            using (var connection = GetConnection(ConnectionName.Passport).EnsureOpen())
            {
                return connection.Query<OAuthUser>(template.Sql, template.Param).FirstOrDefault();
            }
        }
        #endregion OAuthUser

        #region
        public void CachePurge()
        {
            PurgeCache();
        }

        public PagedList<Magic<string, string, int>> CacheGet(int page = 1, int size = 50, int hitCount = 0)
        {
            return GetPagedCache(page, size, hitCount);
        }
        #endregion
    }
}